<?php
class Admin_Model_Announcement extends Application_Model_Db_Db
{
    public function getAllAnnouncementsAvailableForRole($roleId) {
        $sql = "SELECT
                    `announcement`.`id`,
                    `announcement`.`title`,
                    `announcement`.`date_start`,
                    `announcement`.`date_end`,
                    `announcement`.`is_main`
                FROM `announcement`
                INNER JOIN `announcement_space` AS `space`
                    ON `announcement`.`id` = `space`.`announce_id`
                INNER JOIN `role_categories`
                    ON `space`.`category_id` = `role_categories`.`category_id`
                WHERE `role_categories`.`role_id` = ?";
        return $this->_db->fetchAll($sql, $roleId);
    }

    public function getAnnouncement($announcementId) {
        $sql = "SELECT
                    `announcement`.`id`,
                    `announcement`.`title`,
                    `announcement`.`date_start`,
                    `announcement`.`date_end`,
                    `announcement`.`is_main`
                FROM `announcement`
                WHERE `announcement`.`id` = ?";
        return $this->_db->fetchRow($sql, $announcementId);
    }

    public function getAnnouncementCategories($announcementId) {
        $sql = "SELECT
                    `category`.`id`,
                    `category`.`name`
                FROM `category`
                INNER JOIN `announcement_space` AS `space`
                    ON `category`.`id` = `space`.`category_id`
                WHERE `space`.`announce_id` = ?";
        return $this->_db->fetchAll($sql, $announcementId);
    }

    public function saveAnnouncement($data) {
        $this->_db->insert('announcement', $data);
    }

    public function editAnnouncement($announcementId, $data) {
        $where = 'id = ' . $announcementId;
        $this->_db->update('announcement', $data);
    }

    public function deleteAnnouncement($announcementId) {
        $this->_db->delete('announcement', 'id = ' . $announcementId);
    }

    public function clearLinkAnnouncementToCategories($announcementId) {
        $this->_db->delete('announcement_space', 'announce_id = ' . $announcementId);
    }

    public function linkAnnouncementToCategories($announcementId, array $categories) {
        foreach ($categories as $categoryId) {
            $this->_db->insert('announcement_space', array(
                'announce_id' => $announcementId,
                'category_id' => $categoryId
                )
            );
        }
    }
}